package prototype;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.print.DocFlavor.STRING;

public class mastermind {

    private Connection conn;

    public mastermind(Connection kon) {
        this.conn = kon;

    }

    public static mastermind mind() throws SQLException {
        koneksi konno = new koneksi();
        mastermind konn = new mastermind(konno.DBConn());
        return konn;
    }

    public void tambahbarang(pesanan pesan) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "insert into "
                    + " pesanan (id_pesanan, nama_menu, id_pelanggan, status, jumlah)"
                    + " values(?,?,?,?,?)";
            statement = conn.prepareStatement(sql);
            statement.setString(1, pesan.getId_pesanan());
            statement.setString(2, pesan.getNama());
            statement.setString(3, pesan.getId());
            statement.setInt(4, pesan.getJumlah());
            statement.setString(5, pesan.getStatus());

            statement.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }

    public String cari_member(String anggota) throws SQLException {
        CallableStatement status = null;
        String namae = "";
        try {
            conn.setAutoCommit(false);
            System.out.println("nama" + namae);
            String sql = "{CALL carimember(?,?)}";
            status = conn.prepareCall(sql);
            status.setString(1, anggota);
            System.out.println("kode " + anggota);
            status.registerOutParameter(2, java.sql.Types.VARCHAR);
            status.execute();
            namae = status.getString(2);
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (status != null) {
                    status.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
        return namae;
    }

    public String address(String address) throws SQLException {
        CallableStatement status = null;
        String namae = "";
        try {
            conn.setAutoCommit(false);
            System.out.println("nama" + namae);
            String sql = "{CALL carialamat(?,?)}";
            status = conn.prepareCall(sql);
            status.setString(1, address);
            System.out.println("kode " + address);
            status.registerOutParameter(2, java.sql.Types.VARCHAR);
            status.execute();
            namae = status.getString(2);
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (status != null) {
                    status.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
        return namae;
    }

    public void add_member(member anggota) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "insert into "
                    + " member (id, nama, alamat, no_telp)"
                    + " values(?,?,?,?)";
            statement = conn.prepareStatement(sql);
            statement.setString(1, anggota.getId());
            statement.setString(2, anggota.getNama());
            statement.setString(3, anggota.getAlamat());
            statement.setString(4, anggota.getTelp());
            statement.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
        public List<member> list() throws SQLException {
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            conn.setAutoCommit(false);
            state = conn.prepareStatement("select id, nama, alamat, no_telp "
                    + "from member order by id");
            result = state.executeQuery();
            List<member> list = new ArrayList<member>();
            while (result.next()) {
                member kat = new member();
                kat.setId(result.getString("id"));
                kat.setNama(result.getString("nama"));
                kat.setAlamat(result.getString("alamat"));
                kat.setTelp(result.getString("no_telp"));
                list.add(kat);

            }

            conn.commit();
            return list;
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (state != null) {
                    state.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }

    public String cari_nomer(String nomer) throws SQLException {
        CallableStatement status = null;
        String namae = "";
        try {
            conn.setAutoCommit(false);
            System.out.println("nama" + namae);
            String sql = "{CALL cari_nomer(?,?)}";
            status = conn.prepareCall(sql);
            status.setString(1, nomer);
            System.out.println("kode " + nomer);
            status.registerOutParameter(2, java.sql.Types.VARCHAR);
            status.execute();
            namae = status.getString(2);
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (status != null) {
                    status.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
        return namae;
    }

    public void transform(member anggota) throws SQLException {
        String kode = anggota.getId();
        String nama = anggota.getNama();
        String alamat = anggota.getAlamat();
        String telpun = anggota.getTelp();
        String sql = "update member set nama = '" + nama
                + "' , alamat = '" + alamat + "' , no_telp = '" + telpun + "' where id = '" + kode + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
        public void delete_member(member delete) throws SQLException {
        String kode = delete.getId();
        String sql = "delete from member "
                + "where id = '" + kode + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
}
